package kr.or.kead.daegu.global.Service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import kr.or.kead.daegu.global.Config;
import kr.or.kead.daegu.global.DTO.Business;
import kr.or.kead.daegu.global.DTO.Emp;
import kr.or.kead.daegu.global.DTO.Product;
import kr.or.kead.daegu.global.DTO.SaleHist;

public class DbDao {
	private Connection con;
	
	public DbDao(Connection con) {
		this.con = con;
	}

	public void insertItem(Emp emp) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_INSERT[0]);
			pstmt.setString(1, emp.getCode());
			pstmt.setString(2, emp.getName());
			pstmt.setString(3, emp.getGrade());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()+"E"));
		} finally {
			pstmt.close();
		}
	}
	
	public void insertItem(Product product) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_INSERT[1]);
			pstmt.setString(1, product.getpCode());
			pstmt.setString(2, product.getpName());
			pstmt.setInt(3, product.getRegprice());
			pstmt.setInt(4, product.getPrimeprice());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()+"P"));
		} finally {
			pstmt.close();
		}
	}
	
	public void insertItem(Business business) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_INSERT[2]);
			pstmt.setString(1, business.getbCode());
			pstmt.setString(2, business.getbName());
			pstmt.setString(3, business.getbGrade());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()+"B"));
		} finally {
			pstmt.close();
		}
	}
	
	public void insertItem(SaleHist saleHist) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_INSERT[3]);
			pstmt.setString(1, saleHist.getEmpcode());
			pstmt.setString(2, saleHist.getBcode());
			pstmt.setString(3, saleHist.getPcode());
			pstmt.setInt(4, saleHist.getSalecnt());
			pstmt.setInt(5, saleHist.getSalecost());
			pstmt.setDate(6, new Date(saleHist.getSaledate().getTime()));
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()+"H"));
		} finally {
			pstmt.close();
		}
	}
	
	public void updateItem(Emp emp) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_UPDATE[0]);
			pstmt.setString(3, emp.getCode());
			pstmt.setString(1, emp.getName());
			pstmt.setString(2, emp.getGrade());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()));
		} finally {
			pstmt.close();
		}
	}
	
	public void updateItem(Product product) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_UPDATE[1]);
			pstmt.setString(4, product.getpCode());
			pstmt.setString(1, product.getpName());
			pstmt.setInt(2, product.getRegprice());
			pstmt.setInt(3, product.getPrimeprice());
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()));
		} finally {
			pstmt.close();
		}
	}
	
	public void updateItem(Business business) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_UPDATE[2]);
			pstmt.setString(3, business.getbCode());
			pstmt.setString(1, business.getbName());
			pstmt.setString(2, business.getbGrade());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()));
		} finally {
			pstmt.close();
		}
	}
	
	public void deleteItem(Emp emp) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_DELETE[0]);
			pstmt.setString(1, emp.getCode());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(String.valueOf(e.getErrorCode()));
		} finally {
			pstmt.close();
		}
	}
	
	public void deleteItem(Product product) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_DELETE[1]);
			pstmt.setString(1, product.getpCode());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(String.valueOf(e.getErrorCode()));
		} finally {
			pstmt.close();
		}
	}
	
	public void deleteItem(Business business) throws Exception{
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_DELETE[2]);
			pstmt.setString(1, business.getbCode());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(String.valueOf(e.getErrorCode()));
		} finally {
			pstmt.close();
		}
	}
	
	public Emp selectCodeByCode(Emp emp) throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Emp res = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_DELETE_SEARCH[0]);
			pstmt.setString(1, emp.getCode());
			rs = pstmt.executeQuery();
			if (rs.next())
				res = new Emp(rs.getString(1),rs.getString(2),rs.getString(3));
			if (res==null) throw new Exception("NULL");
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()));
		} finally {
			rs.close();
			pstmt.close();
		}
		return res;
	}
	
	public Product selectCodeByCode(Product product) throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Product res = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_DELETE_SEARCH[1]);
			pstmt.setString(1, product.getpCode());
			rs = pstmt.executeQuery();
			if (rs.next())
				res = new Product(rs.getString(1),rs.getString(2),rs.getInt(3),rs.getInt(4));
			if (res==null) throw new Exception("NULL");
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()));
		} finally {
			rs.close();
			pstmt.close();
		}
		return res;
	}

	public Business selectCodeByCode(Business business) throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Business res = null;
		try {
			pstmt = con.prepareStatement(Config.SQL_DELETE_SEARCH[2]);
			pstmt.setString(1, business.getbCode());
			rs = pstmt.executeQuery();
			if (rs.next())
				res = new Business(rs.getString(1),rs.getString(2),rs.getString(3));
			if (res==null) throw new Exception("NULL");
		} catch (SQLException e) {
			throw new Exception(String.valueOf(e.getErrorCode()));
		} finally {
			rs.close();
			pstmt.close();
		}
		return res;
	}
}
